<?php
declare (strict_types = 1);

namespace app\webadmin\controller;

use app\webadmin\BaseController;
use app\webadmin\model\JingmiduWz as JingmiduWzList;
use app\webadmin\validate\JingmiduWzCheck;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Style\Color;
use PhpOffice\PhpSpreadsheet\Style\Font;
use think\facade\Db;
use think\facade\View;
use \think\facade\Log;
use think\exception\ValidateException;
use PhpOffice\PhpSpreadsheet\IOFactory;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Style\Fill;
use PhpOffice\PhpSpreadsheet\Style\Border;
use PhpOffice\PhpSpreadsheet\Calculation\Statistical;
class JingmiduWz extends BaseController
{
    public function index()
    {
        if (request()->isAjax()) {
            $param = get_params();
            $where = array();
            if (!empty($param['keywords'])) {
                $where[] = ['id|nums|data1', 'like', '%' . $param['keywords'] . '%'];
            }
            $where[] = ['uid', '=', get_login_admin('id')];
            $rows = empty($param['limit']) ? get_config('app.page_size') : $param['limit'];
            $content = JingmiduWzList::where($where)
                ->order('id asc')
                ->paginate($rows, false, ['query' => $param])->each(function ($item, $k) {
                    //$item['createtime']=date('Y-m-d H:i:s',$item['create_time']);
                    $item['data1'] = sprint_data(floatval($item['data1']), 2);
                    $item['data2'] = sprint_data(floatval($item['data2']), 2);
                    $item['data3'] = sprint_data(floatval($item['data3']), 2);
                    $item['data4'] = sprint_data(floatval($item['data4']), 2);
                    $item['data5'] = sprint_data(floatval($item['data5']), 2);

                    return $item;
                });
            return table_assign(0, '', $content);
        } else {
            return view();
        }
    }

    //删除数据
    public function delete(){
        //Db::name('sjjy')->where('id','>',0)->delete();
        $id = get_params("id");
        if(empty($id)){
            return to_assign(1, "id不能为空");
        }
        // 软删除
        $db=JingmiduWzList::where('id',$id)->find();
        if($db){
            // 软删除
            $db->delete();
            add_log('delete', $id);
            return to_assign(0, "删除成功");
        } else {
            return to_assign(1, "删除失败");
        }
    }

    //添加/修改
    public function add(){
        $param = get_params();
        if (request()->isAjax()) {
            if (!empty($param['id']) && $param['id'] > 0) {
                //修改
                try {
                    validate(JingmiduWzCheck::class)->scene('edit')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['update_time'] = time();
                $res = JingmiduWzList::strict(false)->field(true)->update($param);
                if ($res) {
                    add_log('edit', $param['id'], $param);
                    return to_assign(0, "修改成功");
                }
                return to_assign();
            } else {
                //新增
                try {
                    validate(JingmiduWzCheck::class)->scene('add')->check($param);
                } catch (ValidateException $e) {
                    // 验证失败 输出错误信息
                    return to_assign(1, $e->getError());
                }
                $param['create_time'] = time();
                $param['uid']=get_login_admin('id');
                $insertId = JingmiduWzList::strict(false)->field(true)->insertGetId($param);
                if ($insertId) {
                    add_log('add', $insertId, $param);
                    return to_assign(0, "添加成功");
                }
                return to_assign();
            }
        }
        else{
            $id = isset($param['id']) ? $param['id'] : 0;
            if ($id > 0) {
                $wdx = JingmiduWzList::where(['id' => $id])->find();
                if($wdx){
                }
                View::assign('data', $wdx);
            }
            View::assign('id', $id);
            return view();
        }
    }

    //清空数据
    public function delete_all(){
        // 软删除
        $param = get_params();
        $db=JingmiduWzList::where('uid',get_login_admin('id'))->delete();
        if($db){

            return to_assign(0, "删除成功");
        } else {
            return to_assign(1, "删除失败");
        }
    }

    public function start_jisuan(){
        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $jingdu=JingmiduWzList::where($where)->order('id asc')->select();

        $j=0;
        $data_array=[];
        $mean_array=[];
        foreach($jingdu as $key=>$v){
            $j++;

            $data_array[]=floatval($v['data1']);
            $data_array[]=floatval($v['data2']);
            $data_array[]=floatval($v['data3']);
            $data_array[]=floatval($v['data4']);
            $data_array[]=floatval($v['data5']);
            $mean_array[]=floatval($v['mean']);
        }


        //所有数和的平方 =SUM(AC4:AH8)^2
        $all_sum_sq=pow(sum_array($data_array),2);

        //所有数的平方和 =SUMSQ(AC4:AH8)
        $all_pingfang_sum=sum_pingfang($data_array);
        //各组内和的平方和
        //=SUMSQ(AI4:AI8)*COUNT(AC4:AH4)^2
        $first_array=[$jingdu[0]['data1'],$jingdu[0]['data2'],$jingdu[0]['data3'],$jingdu[0]['data4'],$jingdu[0]['data5']];
        $allzn_pfh= sum_pingfang($mean_array)*pow(count($first_array),2);

        //自由度 =COUNT(N5:R5)-1
        $zyd=count($first_array)-1;
        //均方MS =U5/V5

        //=COUNT(AC4:AC8)
        $nums1=$j;
        $nums2=count($first_array);
        //=AVERAGE(AC4:AH8)
        $avg1=Statistical::AVERAGE($data_array);
        $sd=sprint_data(Statistical::STDEV($data_array),9);
        //=AE16/AE15*100
        $rsd=sprint_data($sd/$avg1*100,9);
        //组间平方和 =AE11/AC16-AC11/AC15/AC16
        $zj_pfh=sprint_data(($allzn_pfh/$nums2)-(floatval($all_sum_sq)/$nums1/$nums2),3);
        //自由度 =AC15-1
        $zj_zyd=number_format($nums1-1,3);
        //方差=AC18/AD18
        $zj_fc=sprint_data($zj_pfh/$zj_zyd,3);
        //统计量 =AE18/AE19

        //组间内平方和 =AD11-AE11/AC16
        $zn_pfh=sprint_data($all_pingfang_sum-$allzn_pfh/$nums2,3);
        //组内自由度 =AC15*(AC16-1)
        $zn_zyd=number_format($nums1*($nums2-1),3);
        //组内方差 =AC19/AD19
        $zn_fc=sprint_data($zn_pfh/$zn_zyd,3);

        //统计量 =AE18/AE19
        $zj_tjl=sprint_data($zj_fc/$zn_fc,2);
        //=FINV(0.05,AD18,AD19)

        //表3的自由度 =COUNT(N5:R5)-1
        //均方MS =U5/V5
        $ms = number_format($zj_pfh/$zyd,3);

        //组内自由度2 =V5*COUNT(N5:N9)
        $zn_zyd2 = $zyd*$j;
        // 组内均方MS=U6/V6
        $zn_ms =number_format($zn_pfh/$zn_zyd2,3);
        //F值 =W5/W6
        $fz=number_format($ms/$zn_ms,4);

        //组间方差 =(W5-W6)/COUNT(N5:R5)
        $zjfc=($ms-$zn_ms)/$nums2;

        //中间精密度标准差 =(V12+V13)^0.5
        $zjmd_bzc=number_format(pow(($zn_ms+$zjfc),0.5),4);

        //测量不确定度评定 =(W6/3+V13)^0.5
        $bqddpd=number_format(pow(($zn_ms/3+$zjfc),0.5),4);

        //单因素方差分析表
        $html = '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">所有数和的平方</td><td>所有数的平方和</td><td>各组内和的平方和</td></tr>";
        $html .= "<tr><td align=\"center\">{$all_sum_sq}</td><td>{$all_pingfang_sum}</td><td>{$allzn_pfh}</td></tr>";
        $html.='</table>';
        $html.= "<br><br>";
        $html .= '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">数据个数</td><td>{$nums1}</td><td>平均值</td><td>{$avg1}</td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\">重复数</td><td>{$nums2}</td><td>SD值</td><td>{$sd}</td><td>RSD</td><td>{$rsd}</td></tr>";
        $html .= "<tr><td align=\"center\">差来源</td><td>平方和（Q）</td><td>自由度（f）</td><td>方差（S）</td><td>统计量（F）</td><td>F<sub>(0.05,v1,v2)</sub></td></tr>";
        $html .= "<tr><td align=\"center\">组间（A）</td><td>{$zj_pfh}</td><td>{$zj_zyd}</td><td>{$zj_fc}</td><td>{$zj_tjl}</td><td></td></tr>";
        $html .= "<tr><td align=\"center\">组内（e）</td><td>{$zn_pfh}</td><td>{$zn_zyd}</td><td>{$zn_fc}</td><td></td></tr>";

        $html.='</table>';
        $html.= "<br><br>";
        $html .= '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">单因素方差分析表</td><td></td><td></td><td></td><td></td><td></td><td></td></tr>";
        $html .= "<tr><td align=\"center\">变异源</td><td>平方和</td><td>自由度</td><td>均方MS</td><td>F值</td><td>p值</td><td>F临界值</td></tr>";
        $html .= "<tr><td align=\"center\">组间</td><td>{$zj_pfh}</td><td>{$zyd}</td><td>{$ms}</td><td>{$fz}</td><td>0.0524</td><td>2.8661</td></tr>";
        $html .= "<tr><td align=\"center\">组内</td><td>{$zn_pfh}</td><td>{$zn_zyd2}</td><td>{$zn_ms}</td><td></td><td></td><td></td></tr>";
        $html.='</table>';
        $html.= "<br><br>";

        $html .= '<table class="layui-table layui-table-form">';
        $html .= "<tr><td align=\"center\">重复性方差</td><td>S<sup>2</sup><sub>r</sub></td><td>{$zn_ms}</td></tr>";
        $html .= "<tr><td align=\"center\">组间方差</td><td>S<sup>2</sup><sub>b</sub></td><td>{$zjfc}</td></tr>";
        $html .= "<tr><td align=\"center\">中间精密度标准差</td><td>s<sub>ip</sub></td><td>{$zjmd_bzc}</td></tr>";
        $html .= "<tr><td align=\"center\">平行子样个数</td><td>m</td><td>3</td></tr>";
        $html .= "<tr><td align=\"center\">测量不确定度评定</td><td>u</td><td>$bqddpd</td></tr>";

        $html.='</table>';
        $html.= "<br><br>";
        return $html;
    }

    //导出export_data
    public function export_data(){
        $where=[];
        $where[]=['uid','=',get_login_admin('id')];
        $jingdu=JingmiduWzList::where($where)->order('id asc')->select();

        $j=0;
        $data_array=[];
        $mean_array=[];
        foreach($jingdu as $key=>$v){
            $j++;
            $data_array[]=floatval($v['data1']);
            $data_array[]=floatval($v['data2']);
            $data_array[]=floatval($v['data3']);
            $data_array[]=floatval($v['data4']);
            $data_array[]=floatval($v['data5']);
            $mean_array[]=floatval($v['mean']);
        }


        //所有数和的平方 =SUM(AC4:AH8)^2
        $all_sum_sq=pow(sum_array($data_array),2);

        //所有数的平方和 =SUMSQ(AC4:AH8)
        $all_pingfang_sum=sum_pingfang($data_array);
        //各组内和的平方和
        //=SUMSQ(AI4:AI8)*COUNT(AC4:AH4)^2
        $first_array=[$jingdu[0]['data1'],$jingdu[0]['data2'],$jingdu[0]['data3'],$jingdu[0]['data4'],$jingdu[0]['data5']];
        $allzn_pfh= sum_pingfang($mean_array)*pow(count($first_array),2);

        //自由度 =COUNT(N5:R5)-1
        $zyd=count($first_array)-1;
        //均方MS =U5/V5

        //=COUNT(AC4:AC8)
        $nums1=$j;
        $nums2=count($first_array);
        //=AVERAGE(AC4:AH8)
        $avg1=Statistical::AVERAGE($data_array);
        $sd=sprint_data(Statistical::STDEV($data_array),9);
        //=AE16/AE15*100
        $rsd=sprint_data($sd/$avg1*100,9);
        //组间平方和 =AE11/AC16-AC11/AC15/AC16
        $zj_pfh=sprint_data(($allzn_pfh/$nums2)-(floatval($all_sum_sq)/$nums1/$nums2),3);
        //自由度 =AC15-1
        $zj_zyd=number_format($nums1-1,3);
        //方差=AC18/AD18
        $zj_fc=sprint_data($zj_pfh/$zj_zyd,3);
        //统计量 =AE18/AE19

        //组间内平方和 =AD11-AE11/AC16
        $zn_pfh=sprint_data($all_pingfang_sum-$allzn_pfh/$nums2,3);
        //组内自由度 =AC15*(AC16-1)
        $zn_zyd=number_format($nums1*($nums2-1),3);
        //组内方差 =AC19/AD19
        $zn_fc=sprint_data($zn_pfh/$zn_zyd,3);

        //统计量 =AE18/AE19
        $zj_tjl=sprint_data($zj_fc/$zn_fc,2);
        //=FINV(0.05,AD18,AD19)

        //表3的自由度 =COUNT(N5:R5)-1
        //均方MS =U5/V5
        $ms = number_format($zj_pfh/$zyd,3);

        //组内自由度2 =V5*COUNT(N5:N9)
        $zn_zyd2 = $zyd*$j;
        // 组内均方MS=U6/V6
        $zn_ms =number_format($zn_pfh/$zn_zyd2,3);
        //F值 =W5/W6
        $fz=number_format($ms/$zn_ms,4);

        //组间方差 =(W5-W6)/COUNT(N5:R5)
        $zjfc=($ms-$zn_ms)/$nums2;

        //中间精密度标准差 =(V12+V13)^0.5
        $zjmd_bzc=number_format(pow(($zn_ms+$zjfc),0.5),4);

        //测量不确定度评定 =(W6/3+V13)^0.5
        $bqddpd=number_format(pow(($zn_ms/3+$zjfc),0.5),4);
        //导出表格
        $objExcel = new Spreadsheet();
        $objWriter = IOFactory::createWriter($objExcel, 'Xlsx');
        $objActSheet = $objExcel->getActiveSheet(0);
        $objActSheet->setTitle('用有证标准物质评定测量方法精密度不确定度');//设置excel的标题

        //$objActSheet->getStyle('A1:C1')->getFont()->setSize(14);
        //$objActSheet->getStyle('A1:C1')->getFill()->setFillType(Fill::FILL_SOLID)->getStartColor()->setRGB('CCCCCC');
        // 设置列宽
        $objActSheet->getColumnDimension('A')->setWidth(20);
        $objActSheet->getColumnDimension('B')->setWidth(20);
        $objActSheet->getColumnDimension('C')->setWidth(20);
        $objActSheet->getColumnDimension('D')->setWidth(20);
        $objActSheet->getColumnDimension('E')->setWidth(20);
        $objActSheet->getColumnDimension('F')->setWidth(20);
        $objActSheet->getColumnDimension('G')->setWidth(20);

        $objActSheet->getRowDimension(1)->setRowHeight(25);      // 设置行高

        //设置水平、垂直居中
        $styleArray = [
            //设置字体样式
            'font' => [
                'name' => 'Arial',
                'color' => [
                    'rgb' => Color::COLOR_BLACK
                ]
            ],
            //设置边框线样式
            'borders' => [
            ],
            //对齐样式
            'alignment' => [
                'horizontal' => Alignment::HORIZONTAL_CENTER,
                'vertical' => Alignment::VERTICAL_CENTER,
                'wrapText' => true,
            ],
            //是否使用前缀
            'quotePrefix'    => true
        ];
        //第1个表格
        $objActSheet->getStyle('A1:C2')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A1:C2')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A1', '所有数和的平方');
        $objActSheet->setCellValue('B1', '所有数的平方和');
        $objActSheet->setCellValue('C1', '各组内和的平方和');
        $objActSheet->getRowDimension(1)->setRowHeight(25);

        $objActSheet->setCellValue('A2', $all_sum_sq);
        $objActSheet->setCellValue('B2', $all_pingfang_sum);
        $objActSheet->setCellValue('C2', $allzn_pfh);
        $objActSheet->getRowDimension(2)->setRowHeight(25);

        //第2个表格
        $objActSheet->getStyle('A6:F10')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A6:F10')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A6', '数据个数');
        $objActSheet->setCellValue('B6', $nums1);
        $objActSheet->setCellValue('C6', '平均值');
        $objActSheet->setCellValue('D6', $avg1);
        $objActSheet->getRowDimension(6)->setRowHeight(25);

        $objActSheet->setCellValue('A7', '重复数');
        $objActSheet->setCellValue('B7', $nums2);
        $objActSheet->setCellValue('C7', 'SD值');
        $objActSheet->setCellValue('D7', $sd);
        $objActSheet->setCellValue('E7', 'RSD');
        $objActSheet->setCellValue('F7', $rsd);
        $objActSheet->getRowDimension(7)->setRowHeight(25);

        $objActSheet->setCellValue('A8', '差来源');
        $objActSheet->setCellValue('B8', '平方和（Q）');
        $objActSheet->setCellValue('C8', '自由度（f）');
        $objActSheet->setCellValue('D8', '方差（S）');
        $objActSheet->setCellValue('E8', '统计量（F）');
        $objActSheet->setCellValue('F8', 'F(0.05,v1,v2)');
        $objActSheet->getRowDimension(8)->setRowHeight(25);

        $objActSheet->setCellValue('A9', '组间（A）');
        $objActSheet->setCellValue('B9', $zj_pfh);
        //$objActSheet->setCellValue('C9', $zj_zyd);
        $objActSheet->getCell('C9')->setValueExplicit($zj_zyd, DataType::TYPE_STRING);

        $objActSheet->setCellValue('D9', $zj_fc);
        $objActSheet->setCellValue('E9', $zj_tjl);
        $objActSheet->setCellValue('F9', 'F(0.05,v1,v2)');
        $objActSheet->getRowDimension(9)->setRowHeight(25);

        $objActSheet->setCellValue('A10', '组内（e）');
        $objActSheet->setCellValue('B10', $zn_pfh);
        //$objActSheet->setCellValue('C10', $zn_zyd);
        $objActSheet->getCell('C10')->setValueExplicit($zn_zyd, DataType::TYPE_STRING);

        $objActSheet->setCellValue('D10', $zn_fc);
        $objActSheet->setCellValue('E10', '');
        $objActSheet->setCellValue('F10', '');
        $objActSheet->getRowDimension(10)->setRowHeight(25);

        //第3个表格
        $objActSheet->getStyle('A13:G16')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A13:G16')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A13', '单因素方差分析表');
        $objActSheet->setCellValue('B13', '');
        $objActSheet->setCellValue('C13', '');
        $objActSheet->setCellValue('D13', '');
        $objActSheet->setCellValue('E13', '');
        $objActSheet->setCellValue('F13', '');
        $objActSheet->setCellValue('G13', '');
        $objActSheet->getRowDimension(13)->setRowHeight(25);

        $objActSheet->setCellValue('A14', '变异源');
        $objActSheet->setCellValue('B14', '平方和');
        $objActSheet->setCellValue('C14', '自由度');
        $objActSheet->setCellValue('D14', '均方MS');
        $objActSheet->setCellValue('E14', 'F值');
        $objActSheet->setCellValue('F14', 'p值');
        $objActSheet->setCellValue('G14', 'F临界值');
        $objActSheet->getRowDimension(14)->setRowHeight(25);

        $objActSheet->setCellValue('A15', '组间');
        $objActSheet->setCellValue('B15', $zj_pfh);
        $objActSheet->setCellValue('C15', $zyd);
        $objActSheet->setCellValue('D15', $ms);
        $objActSheet->setCellValue('E15', $fz);
        $objActSheet->setCellValue('F15', '0.0524');
        $objActSheet->setCellValue('G15', '2.8661');
        $objActSheet->getRowDimension(15)->setRowHeight(25);

        $objActSheet->setCellValue('A16', '组内');
        $objActSheet->setCellValue('B16', $zn_pfh);
        $objActSheet->setCellValue('C16', $zn_zyd2);
        $objActSheet->setCellValue('D16', $zn_ms);
        $objActSheet->setCellValue('E16', '');
        $objActSheet->setCellValue('F16', '');
        $objActSheet->setCellValue('G16', '');
        $objActSheet->getRowDimension(16)->setRowHeight(25);


        //第4个表格
        $objActSheet->getStyle('A19:C23')->getBorders()->getAllBorders()->setBorderStyle(Border::BORDER_THIN);       // 下边框样式
        $objActSheet->getStyle('A19:C23')->applyFromArray($styleArray);
        $objActSheet->setCellValue('A19', '重复性方差');
        $objActSheet->setCellValue('B19', 'S2r');
        $objActSheet->setCellValue('C19', $zn_ms);
        $objActSheet->getRowDimension(19)->setRowHeight(25);

        $objActSheet->setCellValue('A20', '组间方差');
        $objActSheet->setCellValue('B20', 'S2b');
        $objActSheet->setCellValue('C20', $zjfc);
        $objActSheet->getRowDimension(20)->setRowHeight(25);

        $objActSheet->setCellValue('A21', '中间精密度标准差');
        $objActSheet->setCellValue('B21', 'sip');
        $objActSheet->setCellValue('C21', $zjmd_bzc);
        $objActSheet->getRowDimension(21)->setRowHeight(25);

        $objActSheet->setCellValue('A22', '平行子样个数');
        $objActSheet->setCellValue('B22', 'm');
        $objActSheet->setCellValue('C22', '3');
        $objActSheet->getRowDimension(22)->setRowHeight(25);

        $objActSheet->setCellValue('A23', '测量不确定度评定');
        $objActSheet->setCellValue('B23', 'u');
        $objActSheet->setCellValue('C23', $bqddpd);
        $objActSheet->getRowDimension(23)->setRowHeight(25);

        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="用有证标准物质评定测量方法精密度不确定度（'. date('Y-m-d')  .'）'.'.xlsx"');
        header('Cache-Control: max-age=0');
        $objWriter->save('php://output');
    }

}